﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

namespace DataAccess.SqlServer
{
    public class SqlServerHopDongThinhGiang: IHopDongThinhGiang
    {

        public HopDongThinhGiang GetHopDongThinhGiang(int oid)
        {
            string sql = @" Select a.SoHopDong, a.NgayKy, a.ChucVuNguoiKy, a.NguoiKy, a.NguoiLaoDong, a.DiaDiemLamViec, a.ChucDanhChuyenMon, a.LoaiHopDong,
                                   b.QuanLyHopDongThinhGiang, b.LoaiHopDong, b.MonDay, b.Lop, b.SoTiet, b.SoTienMotTiet
                            From HopDong a inner join HopDongThinhGiang b on a.Oid = b.Oid
                            Where Oid = @Oid";
            SqlParameter param = new SqlParameter("@Oid", oid);
            DataRow row = DataProvider.GetDataRow(sql, param);

            HopDongThinhGiang hopDongThinhGiang = new HopDongThinhGiang();

            hopDongThinhGiang.Oid = oid;
            hopDongThinhGiang.SoHopDong = row["SoHopDong"].ToString();
            hopDongThinhGiang.NgayKy = DateTime.Parse(row["NgayKy"].ToString());
            hopDongThinhGiang.NgayKy = DateTime.Parse(row["NgayKy"].ToString());
            int id;
            if (int.TryParse(row["ChucVuNguoiKy"].ToString(), out id))
            {
                SqlServerChucVu sqlServerChucVu = new SqlServerChucVu();
                hopDongThinhGiang.ChucVuNguoiKy = sqlServerChucVu.GetChucVu(id);
            }
            if (int.TryParse(row["NguoiKy"].ToString(), out id))
            {
                SqlServerNhanVien sqlServerNhanVien = new SqlServerNhanVien();
                hopDongThinhGiang.NguoiKy = sqlServerNhanVien.GetNhanVien(id);
            }
            if (int.TryParse(row["NguoiLaoDong"].ToString(), out id))
            {
                SqlServerHoSo sqlServerHoSo = new SqlServerHoSo();
                hopDongThinhGiang.NguoiLaoDong = sqlServerHoSo.GetHoSo(id);
            }
            if (int.TryParse(row["DiaDiemLamViec"].ToString(), out id))
            {
                SqlServerBoPhan sqlServerBoPhan = new SqlServerBoPhan();
                hopDongThinhGiang.DiaDiemLamViec = sqlServerBoPhan.GetBoPhan(id);
            }
            hopDongThinhGiang.ChucDanhChuyenMon = row["ChucDanhChuyenMon"].ToString();

            if (int.TryParse(row["QuanLyHopDongThinhGiang"].ToString(), out id))
            {
                SqlServerQuanLyHopDongThinhGiang sqlServerQuanLyHopDongThinhGiang = new SqlServerQuanLyHopDongThinhGiang();
                hopDongThinhGiang.QuanLyHopDongThinhGiang = sqlServerQuanLyHopDongThinhGiang.GetQuanLyHopDongThinhGiang(id);
            }
            hopDongThinhGiang.MonDay = row["MonDay"].ToString();
            hopDongThinhGiang.Lop = row["Lop"].ToString();
            hopDongThinhGiang.SoTiet = decimal.Parse(row["SoTiet"].ToString());
            hopDongThinhGiang.SoTienMotTiet = decimal.Parse(row["SoTienMotTiet"].ToString());

            return hopDongThinhGiang;
        }

        public List<HopDongThinhGiang> GetListHopDongThinhGiang()
        {
            string sql = @" Select a.Oid, a.SoHopDong, a.NgayKy, a.ChucVuNguoiKy, a.NguoiKy, a.NguoiLaoDong, a.DiaDiemLamViec, a.ChucDanhChuyenMon, a.LoaiHopDong,
                                   b.QuanLyHopDongThinhGiang, b.LoaiHopDong, b.MonDay, b.Lop, b.SoTiet, b.SoTienMotTiet
                            From HopDong a inner join HopDongThinhGiang b on a.Oid = b.Oid";

            DataTable dt = DataProvider.GetDataTable(sql);
            List<HopDongThinhGiang> list = new List<HopDongThinhGiang>();

            foreach (DataRow row in dt.Rows)
            {
                HopDongThinhGiang hopDongThinhGiang = new HopDongThinhGiang();

                hopDongThinhGiang.Oid = int.Parse(row["Oid"].ToString());
                hopDongThinhGiang.SoHopDong = row["SoHopDong"].ToString();
                hopDongThinhGiang.NgayKy = DateTime.Parse(row["NgayKy"].ToString());
                hopDongThinhGiang.NgayKy = DateTime.Parse(row["NgayKy"].ToString());
                int id;
                if (int.TryParse(row["ChucVuNguoiKy"].ToString(), out id))
                {
                    SqlServerChucVu sqlServerChucVu = new SqlServerChucVu();
                    hopDongThinhGiang.ChucVuNguoiKy = sqlServerChucVu.GetChucVu(id);
                }
                if (int.TryParse(row["NguoiKy"].ToString(), out id))
                {
                    SqlServerNhanVien sqlServerNhanVien = new SqlServerNhanVien();
                    hopDongThinhGiang.NguoiKy = sqlServerNhanVien.GetNhanVien(id);
                }
                if (int.TryParse(row["NguoiLaoDong"].ToString(), out id))
                {
                    SqlServerHoSo sqlServerHoSo = new SqlServerHoSo();
                    hopDongThinhGiang.NguoiLaoDong = sqlServerHoSo.GetHoSo(id);
                }
                if (int.TryParse(row["DiaDiemLamViec"].ToString(), out id))
                {
                    SqlServerBoPhan sqlServerBoPhan = new SqlServerBoPhan();
                    hopDongThinhGiang.DiaDiemLamViec = sqlServerBoPhan.GetBoPhan(id);
                }
                hopDongThinhGiang.ChucDanhChuyenMon = row["ChucDanhChuyenMon"].ToString();

                if (int.TryParse(row["QuanLyHopDongThinhGiang"].ToString(), out id))
                {
                    SqlServerQuanLyHopDongThinhGiang sqlServerQuanLyHopDongThinhGiang = new SqlServerQuanLyHopDongThinhGiang();
                    hopDongThinhGiang.QuanLyHopDongThinhGiang = sqlServerQuanLyHopDongThinhGiang.GetQuanLyHopDongThinhGiang(id);
                }
                hopDongThinhGiang.MonDay = row["MonDay"].ToString();
                hopDongThinhGiang.Lop = row["Lop"].ToString();
                hopDongThinhGiang.SoTiet = decimal.Parse(row["SoTiet"].ToString());
                hopDongThinhGiang.SoTienMotTiet = decimal.Parse(row["SoTienMotTiet"].ToString());

                list.Add(hopDongThinhGiang);
            }
            return list;
        }

        public int Insert(HopDongThinhGiang obj)
        {
            SqlServerHopDong sqlServerHopDong = new SqlServerHopDong();
            int oid = sqlServerHopDong.Insert((HopDong)obj);
            if (oid > 0)
            {
                string sql = @" Insert into HopDongThinhGiang(Oid, QuanLyHopDongThinhGiang, LoaiHopDong, MonDay, Lop, SoTiet, SoTienMotTiet)
                                values(@Oid, @QuanLyHopDongThinhGiang, @LoaiHopDong, @MonDay, @Lop, @SoTiet, @SoTienMotTiet)";
                SqlParameter[] param = {
                                           new SqlParameter("@Oid", oid),
                                           new SqlParameter("@QuanLyHopDongThinhGiang", obj.QuanLyHopDongThinhGiang),
                                           new SqlParameter("@LoaiHopDong", obj.PhanLoai),
                                           new SqlParameter("@MonDay", obj.MonDay),
                                           new SqlParameter("@Lop", obj.Lop),
                                           new SqlParameter("@SoTiet", obj.SoTiet),
                                           new SqlParameter("@SoTienMotTiet", obj.SoTienMotTiet)
                                        };
                int temp = DataProvider.Insert(sql, true, param);

                if (temp > 0)
                {
                    ThamChieuDuLieu thamChieuDuLieu = new ThamChieuDuLieu();
                    thamChieuDuLieu.Cha = new SqlServerKieuDuLieu().GetKieuDuLieu(typeof(HopDong).ToString());
                    thamChieuDuLieu.Con = new SqlServerKieuDuLieu().GetKieuDuLieu(typeof(HopDongThinhGiang).ToString());
                    thamChieuDuLieu.GiaTri = oid;

                    SqlServerThamChieuDuLieu sqlServerThamChieuDuLieu = new SqlServerThamChieuDuLieu();
                    sqlServerThamChieuDuLieu.Insert(thamChieuDuLieu);
                }
            }
            return oid;
        }

        public int Update(HopDongThinhGiang obj)
        {
            SqlServerHopDong sqlServerHopDong = new SqlServerHopDong();
            int temp = sqlServerHopDong.Update((HopDong)obj);

            if (temp > 0)
            {
                string sql = @" Update HopDongThinhGiang
                            Set  
                                QuanLyHopDongThinhGiang = @QuanLyHopDongThinhGiang, 
                                LoaiHopDong = @LoaiHopDong, 
                                MonDay = @MonDay, 
                                Lop = @Lop, 
                                SoTiet = @SoTiet, 
                                SoTienMotTiet = @SoTienMotTiet
                            Where Oid = @Oid";
                SqlParameter[] param = {
                                           new SqlParameter("@Oid", obj.Oid),
                                           new SqlParameter("@QuanLyHopDongThinhGiang", obj.QuanLyHopDongThinhGiang),
                                           new SqlParameter("@LoaiHopDong", obj.PhanLoai),
                                           new SqlParameter("@MonDay", obj.MonDay),
                                           new SqlParameter("@Lop", obj.Lop),
                                           new SqlParameter("@SoTiet", obj.SoTiet),
                                           new SqlParameter("@SoTienMotTiet", obj.SoTienMotTiet)
                                        };
                temp = DataProvider.Update(sql, param);
            }
            return temp;
        }

        public int Delete(HopDongThinhGiang obj)
        {
            string sql = @" Delete From HopDongThinhGiang Where Oid = @Oid";

            SqlParameter param = new SqlParameter("@Oid", obj.Oid);
            int temp = DataProvider.Update(sql, param);
            if (temp > 0)
            {
                SqlServerHopDong sqlServerHopDong = new SqlServerHopDong();
                temp = sqlServerHopDong.Delete((HopDong)obj);

                if (temp > 0)
                {
                    ThamChieuDuLieu thamChieuDuLieu = new ThamChieuDuLieu();
                    thamChieuDuLieu.GiaTri = obj.Oid;

                    SqlServerThamChieuDuLieu sqlServerThamChieuDuLieu = new SqlServerThamChieuDuLieu();
                    temp = sqlServerThamChieuDuLieu.Delete(thamChieuDuLieu);
                }
            }
            return temp;
        }


        public int Save(HopDongThinhGiang obj)
        {
            throw new NotImplementedException();
        }
    }
}
